import java.awt.*;
import java.awt.event.*;
import java.awt.image.*;
import javax.swing.*;
import java.util.*;
import javax.swing.table.*;
import java.util.*;
import java.sql.*;
import javax.swing.JTextField.*;
import javax.swing.text.MaskFormatter;
import javax.swing.JFormattedTextField;
import java.text.ParseException;

public class appointmentsPage extends MainFrame
{/*Ichie here*/
  int page = 1;
  
  JLabel title;
  editAppointmentsFrame editFrame;
  ArrayList <Integer> key = new ArrayList();
  static int picked;
  static boolean fromPatients = false;
  
  /* Appointments list page */
  JComboBox searchFilter;
  JComboBox filter;
  JTextField searchBox;
  JButton btnSearch;
  JRadioButton all;
  JRadioButton pending;
  JRadioButton completed;
  JRadioButton late;
  ButtonGroup bgroup;
  DefaultTableModel model;
  JTable table;
  JScrollPane scrollPane;
  String row[][] = {{"Today","11:30-13:30","Maria Regina Bustos","Prophylaxis","PENDING"}};
  String col[] = {"Date","Time","Service Type","Patient","Status"};
  
    /* Appointments Details page*/
  JTextArea progress;
  JScrollPane areascrollPane;
  JTextArea comments;
  JScrollPane commentsPane;
  JButton btnEdit;
  JButton btnDelete;
  JButton btnPatient;
  
  /* Schedule new appointment*/
  ArrayList <Integer> servicepicked = new ArrayList();
  ArrayList <Integer> patientpicked = new ArrayList();
  ArrayList <Integer> time = new ArrayList();
  JComboBox service;
  JComboBox patient;
  //JTextField date;
  //JTextField startTime;
  JTextField endTime;
  JButton btnReserve;
  
  JFormattedTextField startTime = null;
  JFormattedTextField date = null;
  MaskFormatter dateMask = null;
  MaskFormatter timeMask = null;
  JLabel dateFormat;
  JLabel start;
  JLabel end;



  
   public void Draw(){
       switch(page){
       
           case 1:
                title = new JLabel("View Appointments");
                title.setFont(new Font("Cambria", Font.PLAIN, 24));
                title.setForeground(Color.black);
                title.setBounds(10,215,400,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Search");
                title.setFont(new Font("Cambria", Font.PLAIN, 18));
                title.setForeground(Color.black);
                title.setBounds(30,250,300,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Search Results");
                title.setFont(new Font("Cambria", Font.PLAIN, 18));
                title.setForeground(Color.black);
                title.setBounds(30,340,300,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Status:");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,310,80,40);
                MainFrame.panel.add(title);
                
                filter = new JComboBox();
                filter.setBackground(Color.WHITE);
                filter.setBounds(230,290,190,25);
                filter.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                filter.setVisible(false);
                MainFrame.panel.add(filter);
            
                
                  /* Search filter Combobox */
                  searchFilter = new JComboBox();
                  searchFilter.addItem("All");
                  searchFilter.addItem("Service Type");
                  searchFilter.addItem("Patient");
                  searchFilter.addItem("Date");
                  searchFilter.addItem("Time");
                  searchFilter.setBackground(Color.WHITE);
                  searchFilter.setBounds(30,290,190,25);
                  searchFilter.setSelectedIndex(0);
                  searchFilter.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                  searchFilter.addActionListener(new ComboBoxListener());
                  MainFrame.panel.add(searchFilter);
                  searchFilter.updateUI();
            
            /* Search Box input */
            searchBox = new JTextField(10);
            searchBox.setBounds(230,290,190,25);
            searchBox.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
            searchBox.setEnabled(false);
            MainFrame.panel.add(searchBox);
            
            /* Search Button */
            btnSearch = new JButton(new ImageIcon("searchButton.jpg"));
            btnSearch.setBounds(430,290,70,25);
            btnSearch.addActionListener(new ButtonListener());
            MainFrame.panel.add(btnSearch);
                /* Radio buttons */
                // http://www.leepoint.net/notes-java/GUI/components/50radio_buttons/25radiobuttons.html
                bgroup = new ButtonGroup();
                
                all   = new JRadioButton("All"  , true);
                all.setBounds(110,310,50,40);
                all.setOpaque(false);
                all.setFont(new Font("Cambria", Font.PLAIN, 14));
                bgroup.add(all);
                MainFrame.panel.add(all);
                
                pending   = new JRadioButton("Pending", false);
                pending.setBounds(180,310,80,40);
                pending.setOpaque(false);
                pending.setFont(new Font("Cambria", Font.PLAIN, 14));
                bgroup.add(pending);
                MainFrame.panel.add(pending);
                
                completed = new JRadioButton("Completed", false);
                completed.setBounds(280,310,110,40);
                completed.setOpaque(false);
                completed.setFont(new Font("Cambria", Font.PLAIN, 14));
                bgroup.add(completed);
                MainFrame.panel.add(completed);
                
                late = new JRadioButton("Late", false);
                late.setBounds(390,310,70,40);
                late.setOpaque(false);
                late.setFont(new Font("Cambria", Font.PLAIN, 14));
                bgroup.add(late);
                MainFrame.panel.add(late);
                
                /* Appointments Table */
                model = new DefaultTableModel(row,col) {
                /* Override Cell modification for users not to modify data inside table */
                @Override
                public boolean isCellEditable(int row, int column) {
                    //all cells false
                    return false;
                }
               };;
              
              table = new JTable(model);
              model.getDataVector().removeAllElements();
              key.clear();
             try{
                        database.stmt = database.conn.createStatement();
                        // Execute the query
                               
                        database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                
                                while( database.rs.next() ){
                                    key.add(database.rs.getInt("appointmentID"));
                                    model.insertRow(table.getRowCount(),new Object[]{
                                        database.rs.getString("appointmentDate").equals(homePage.dateToday) ? "Today" : database.rs.getString("appointmentDate"),
                                        database.rs.getString("startTime") + "-" + database.rs.getString("endTime"),
                                        database.rs.getString("serviceName"),
                                        database.rs.getString("lastname")+", "+ database.rs.getString("firstName"),
                                        database.rs.getString("status") 
                                    });
                           }
             } catch(SQLException se){}

               table.setFont(new java.awt.Font("Cambria", Font.PLAIN ,15));
               table.getTableHeader().setFont( new Font( "Cambria" , Font.PLAIN , 15 ));
               table.getTableHeader().setBackground( new Color(0, 120, 255, 70) );
               table.getTableHeader().setEnabled(false);
               table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
               table.addMouseListener(new TableListener());
               scrollPane = new JScrollPane(table);
               scrollPane.setBounds(30,380,680,150);
               scrollPane.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
               MainFrame.panel.add(scrollPane);
                
                
                
                break;
                
           case 2:
                title = new JLabel("View Appointments");
                title.setFont(new Font("Cambria", Font.PLAIN, 24));
                title.setForeground(Color.black);
                title.setBounds(10,215,400,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Appointment Details ");
                title.setFont(new Font("Cambria", Font.PLAIN, 18));
                title.setForeground(Color.black);
                title.setBounds(30,250,200,50);
                MainFrame.panel.add(title);
            
                title = new JLabel("Service Type:");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,280,170,50);
                MainFrame.panel.add(title);
       
                title = new JLabel("Patient: ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,315,170,50);
                MainFrame.panel.add(title);
       
                title = new JLabel("Date: ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,350,250,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Start Time: ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,385,250,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("End Time: ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,420,250,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Status: ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(30,455,250,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Progress:");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(340,280,170,50);
                MainFrame.panel.add(title);
                
                title = new JLabel("Comment/s : ");
                title.setFont(new Font("Cambria", Font.PLAIN, 14));
                title.setForeground(Color.black);
                title.setBounds(340,380,250,50);
                MainFrame.panel.add(title);
                
                 try{
                        database.stmt = database.conn.createStatement();
                               
                        database.rs = database.stmt.executeQuery( 
                                "SELECT * FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.APPOINTMENTID =" + picked);
                                
                                while( database.rs.next() ){
                                    title = new JLabel(database.rs.getString("appointmentDate"));
                                    title.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    title.setForeground(Color.black);
                                    title.setBounds(140,350,250,50);
                                    MainFrame.panel.add(title);
                                    
                                    title = new JLabel(database.rs.getString("startTime"));
                                    title.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    title.setForeground(Color.black);
                                    title.setBounds(140,385,170,50);
                                    MainFrame.panel.add(title);
                
                                    title = new JLabel(database.rs.getString("endTime"));
                                    title.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    title.setForeground(Color.black);
                                    title.setBounds(140,420,170,50);
                                    MainFrame.panel.add(title);
                                    
                                    title = new JLabel(database.rs.getString("status"));
                                    title.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    title.setForeground(Color.black);
                                    title.setBounds(140,455,170,50);
                                    MainFrame.panel.add(title);
                                    
                                     btnPatient = new JButton("<HTML><u>" + database.rs.getString("firstName")+ " " + database.rs.getString("lastName")+ "</U></HTML>");
                                     btnPatient.setFont(new Font("Cambria", Font.PLAIN, 14));
                                     btnPatient.setForeground(new Color(100, 125, 250));
                                     btnPatient.setBounds(123,325,170,50);
                                     btnPatient.setSize(btnPatient.getPreferredSize());
                                     btnPatient.setBorderPainted(false);
                                     btnPatient.setContentAreaFilled(false);
                                     btnPatient.addActionListener(new ButtonListener());
                                     MainFrame.panel.add(btnPatient);
                                    
                                    title = new JLabel(database.rs.getString("serviceName"));
                                    title.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    title.setForeground(Color.black);
                                    title.setBounds(140,280,170,50);
                                    MainFrame.panel.add(title);

                                    progress = new JTextArea(database.rs.getString("progress"));
                                    progress.setLineWrap(true);
                                    progress.setOpaque(false);
                                    progress.setWrapStyleWord(true);
                                    progress.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    progress.setEditable(false);
                                    areascrollPane = new JScrollPane(progress);
                                    areascrollPane.setBounds(440,295,270,95);
                                    areascrollPane.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
                                    areascrollPane.setBorder(null); 
                                    areascrollPane.setOpaque(false);
                                    areascrollPane.getViewport().setOpaque(false);
                                    MainFrame.panel.add(areascrollPane);
                                    progress.updateUI();
                                    
                                    comments = new JTextArea(database.rs.getString("commentsAndSuggestions"));
                                    comments.setLineWrap(true);
                                    comments.setWrapStyleWord(true);
                                    comments.setOpaque(false);
                                    comments.setFont(new Font("Cambria", Font.PLAIN, 14));
                                    comments.setEditable(false);
                                    commentsPane = new JScrollPane(comments);
                                    commentsPane.setBounds(440,395,270,95);
                                    commentsPane.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
                                    commentsPane.setBorder(null); 
                                    commentsPane.setOpaque(false);
                                    commentsPane.getViewport().setOpaque(false);
                                    MainFrame.panel.add(commentsPane);
                                    comments.updateUI();
                                   
                           }
                } catch(SQLException se){}
                
                
                 /* Edit Appointment Button opens up a new frame*/
                btnEdit = new JButton(new ImageIcon("editButton.jpg"));
                btnEdit.setBounds(250,550,92,25);
                btnEdit.addActionListener(new ButtonListener());
                panel.add(btnEdit);
            
                /* Delete Appointment Button */
                btnDelete = new JButton(new ImageIcon("deleteButton.jpg"));
                btnDelete.setBounds(352,550,92,25);
                btnDelete.addActionListener(new ButtonListener());
                MainFrame.panel.add(btnDelete);
                
                break;
                 
                 /* Schedule Appointment*/
           case 3:
                title = new JLabel("Schedule New Appointment");
                title.setFont(new Font("Cambria", Font.PLAIN, 24));
                title.setForeground(Color.black);
                title.setBounds(10,215,400,50);
                MainFrame.panel.add(title);
                
                 title = new JLabel("Type of Service: ");
                 title.setFont(new Font("Cambria", Font.PLAIN, 14));
                 title.setForeground(Color.black);
                 title.setBounds(30,280,250,50);
                 MainFrame.panel.add(title);
            
            
                 title = new JLabel("Patient to be Handled: ");
                 title.setFont(new Font("Cambria", Font.PLAIN, 14));
                 title.setForeground(Color.black);
                 title.setBounds(30,315,170,50);
                 MainFrame.panel.add(title);
       
                 title = new JLabel("Date: ");
                 title.setFont(new Font("Cambria", Font.PLAIN, 14));
                 title.setForeground(Color.black);
                 title.setBounds(30,350,170,50);
                 MainFrame.panel.add(title);
            
                 title = new JLabel("Start Time: ");
                 title.setFont(new Font("Cambria", Font.PLAIN, 14));
                 title.setForeground(Color.black);
                 title.setBounds(30,385,170,50);
                 MainFrame.panel.add(title);

                 title = new JLabel("End Time: ");
                 title.setFont(new Font("Cambria", Font.PLAIN, 14));
                 title.setForeground(Color.black);
                 title.setBounds(30,420,170,50);
                 MainFrame.panel.add(title);
                 
                 /*statement invalidation for Start Time*/
                 start = new JLabel("       ");
                 start.setFont(new Font("Cambria", Font.PLAIN, 14));
                 start.setForeground(Color.red);
                 start.setBounds(425,390,200,60);
                 MainFrame.panel.add(start);
                 /*start.setText("Invalid Start time"); --> use this for invalidation*/
                 /*end.setText("         "); --> use this for invalidation*/
                 
                 /*statement invalidation fo End Time*/
                 end = new JLabel("     ");
                 end.setFont(new Font("Cambria", Font.PLAIN, 14));
                 end.setForeground(Color.red);
                 end.setBounds(425,420,200,60);
                 MainFrame.panel.add(end);
                 /*end.setText("Invalid End time"); --> use this for invalidation*/
                 /*end.setText("         "); --> use this for invalidation*/

                 
                  try{
                        /* Service Combobox */
                        MainFrame.database.stmt = MainFrame.database.conn.createStatement();
                        MainFrame.database.rs = MainFrame.database.stmt.executeQuery("SELECT * FROM SERVICE ORDER BY SERVICENAME");
                               
                        servicepicked.clear();
                        service = new JComboBox();
                        while(MainFrame.database.rs.next() ){
                           servicepicked.add(MainFrame.database.rs.getInt("serviceId"));
                           service.addItem(MainFrame.database.rs.getString("serviceName"));
                        }
                        
                        
                        /* Patient Combobox */
                        MainFrame.database.stmt = MainFrame.database.conn.createStatement();
                        MainFrame.database.rs = MainFrame.database.stmt.executeQuery("SELECT * FROM PATIENT ORDER BY LASTNAME");
                               
                        patientpicked.clear();
                        patient = new JComboBox();
                        while(MainFrame.database.rs.next() ){
                           patientpicked.add(MainFrame.database.rs.getInt("patientId"));
                           patient.addItem(MainFrame.database.rs.getString("lastName")+", "+MainFrame.database.rs.getString("firstName"));
                        }
                        
                        if(fromPatients == true)
                            patient.setSelectedIndex(patientpicked.indexOf(picked));
                        else
                            patient.setSelectedIndex(0);
                        
                        fromPatients = false;
                        
                        patient.setBackground(Color.WHITE);
                        patient.setBounds(220,330,190,25);
                        patient.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                        panel.add(patient);
 
                        service.setSelectedIndex(0);
                        service.setBackground(Color.WHITE);
                        service.setBounds(220,295,190,25);
                        service.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                        panel.add(service);
                        service.updateUI();
                     }catch(Exception ex){}
                 
             
                  /* date input
                  date = new JTextField("YYYY-MM-DD");
                  date.setBounds(220,365,190,25);
                  date.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                  MainFrame.panel.add(date);*/

                  /*start time input 
                  startTime = new JTextField(10);
                  startTime.setBounds(220,420,190,25);
                  startTime.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                  MainFrame.panel.add(startTime);*/
            
            
                  /* end Time input 
                  endTime = new JTextField(10);
                  endTime.setBounds(220,435,190,25);
                  endTime.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                  MainFrame.panel.add(endTime);*/
                  
                  /*formatted Date field*/
                  try {
                        dateMask = new MaskFormatter("####-##-##");
                        dateMask.setPlaceholderCharacter('#');
                        dateMask.setPlaceholder("YYYY-MM-DD");
                        
                    } catch (ParseException e) { e.printStackTrace();} 
                  
                  date = new JFormattedTextField(dateMask);
                  date.setBounds(220,365,190,25);
                  date.setFont(new java.awt.Font("Cambria",java.awt.Font.PLAIN,14));
                  MainFrame.panel.add(date);
                  
                  /*formatted Time field*/
                  try {
                        timeMask = new MaskFormatter("##:##:##");
                        timeMask.setPlaceholderCharacter('#');
                        timeMask.setPlaceholder("HH:MM:SS");
                        
                    } catch (ParseException e) { e.printStackTrace();}
                    
                    startTime = new JFormattedTextField(timeMask);
                    startTime.setBounds(220,400,190,25);
                    startTime.setFont(new java.awt.Font("Cambria", java.awt.Font.PLAIN,14));
                    MainFrame.panel.add(startTime);
                    
                  /*formatted Time field*/
                    endTime = new JFormattedTextField(timeMask);
                    endTime.setBounds(220,435,190,25);
                    endTime.setFont(new java.awt.Font("Cambria", java.awt.Font.PLAIN,14));
                    MainFrame.panel.add(endTime);
                  
                  
                  /* Reserve Button */
                  btnReserve = new JButton(new ImageIcon("reserveButton.jpg"));
                  btnReserve.setBounds(140,490,145,25);
                  btnReserve.addActionListener(new ButtonListener());
                  MainFrame.panel.add(btnReserve);
        
       }
       
       title = new JLabel("_____________________________________________________________");
       title.setFont(new Font("Cambria", Font.BOLD, 14));
       title.setForeground(Color.black);
       title.setBounds(0,220,400,50);
       MainFrame.panel.add(title);
  }
  
  public class ButtonListener implements ActionListener{

     public void actionPerformed(ActionEvent e){
      if(e.getSource() == btnSearch){
             model.setNumRows(0);
             try{
                        database.stmt = database.conn.createStatement();
                        // Execute the query
                        switch(searchFilter.getSelectedIndex()){
                            case 0:
                            if(all.isSelected() == true)
                            database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(pending.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Pending'" 
                                + " AND p.PATIENTID = q.PATIENTID ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(late.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Late'" 
                                + " AND p.PATIENTID = q.PATIENTID ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(completed.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT COUNT(*) AS counter, appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Completed'" 
                                + " AND p.PATIENTID = q.PATIENTID ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                break;
                                
                            case 1:
                            if(all.isSelected() == true)
                            database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.SERVICEID = "+ servicepicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(pending.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Pending'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.SERVICEID = "+ servicepicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(late.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Late'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.SERVICEID = "+ servicepicked.get(filter.getSelectedIndex()) +" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(completed.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT COUNT(*) AS counter, appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Completed'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.SERVICEID = "+ servicepicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                break;
                                
                            case 2:
                            if(all.isSelected() == true)
                            database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.PATIENTID = "+ patientpicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(pending.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Pending'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.PATIENTID = "+ patientpicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(late.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Late'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.PATIENTID = "+ patientpicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                else if(completed.isSelected() == true)
                                database.rs = database.stmt.executeQuery( 
                                "SELECT COUNT(*) AS counter, appointmentId, DATE_FORMAT(appointmentDate, '%M %d %Y') as appointmentDate, startTime,endTime,serviceName,lastName,firstName,status FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID AND p.STATUS LIKE 'Completed'" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.PATIENTID = "+ patientpicked.get(filter.getSelectedIndex())+" ORDER BY p.APPOINTMENTDATE DESC, p.STARTTIME ASC");
                                break;
                                
                                
                        }
                        
                           key.clear();
                           while( database.rs.next() ){

                                    key.add(database.rs.getInt("appointmentID"));
                                    model.insertRow(table.getRowCount(),new Object[]{
                                        database.rs.getString("appointmentDate").equals(homePage.dateToday) ? "Today" : database.rs.getString("appointmentDate"),
                                        database.rs.getString("startTime") + "-" + database.rs.getString("endTime"),
                                        database.rs.getString("serviceName"),
                                        database.rs.getString("lastname")+", "+ database.rs.getString("firstName"),
                                        database.rs.getString("status")
                                    });
                                
                           }
                           

             } catch(Exception ex){}
        }
       else if(e.getSource() == btnReserve){
           JDialog.setDefaultLookAndFeelDecorated(false);
           int response = JOptionPane.showConfirmDialog(null, "Are you sure you want to schedule this appointment?", 
           "Schedule New Appointment", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
           if (response == JOptionPane.YES_OPTION){   
               try{
                    MainFrame.database.stmt = MainFrame.database.conn.createStatement();
                        
                    int numUpdated = 
                    MainFrame.database.stmt.executeUpdate("INSERT INTO APPOINTMENT (appointmentDate,startTime,endTime,progress,commentsAndSuggestions, serviceId, patientId, status)" +
                    " VALUES ('"+date.getText()+"','" + startTime.getText()+"','"+endTime.getText()+"',NULL,NULL,'" +servicepicked.get(service.getSelectedIndex())+"','"
                    +patientpicked.get(patient.getSelectedIndex())+"','"+"Pending"+"')" ); 
                    
                    
                    MainFrame.database.stmt = MainFrame.database.conn.createStatement();           
                    MainFrame.database.rs = MainFrame.database.stmt.executeQuery("SELECT * FROM APPOINTMENT ORDER BY appointmentId DESC LIMIT 1");
                    
                    while (MainFrame.database.rs.next() )
                        picked = MainFrame.database.rs.getInt("appointmentId");
                    
                    JOptionPane.showMessageDialog(null,"Appointment was successfully added.");
                    
                    MainFrame.panel.removeAll();
                    MainFrame.appointmentsPage.page = 2;
                    MainFrame.appointmentsPage.Draw();
                    MainFrame.header.Draw();
                    MainFrame.panel.add(MainFrame.backgroundImage);
                    MainFrame.panel.repaint();
                    //JOptionPane.showMessageDialog(null,ex.getMessage());
               }catch( Exception ex ){System.out.println("Message "+ex.getMessage());}
            }
       }
       else if(e.getSource() == btnEdit){
            editFrame = new editAppointmentsFrame(new JFrame(), "Edit Appointment Details");
            MainFrame.frame.setEnabled(false);
            editFrame.MainFrame(); 
      }
      else if(e.getSource() == btnDelete){
           JDialog.setDefaultLookAndFeelDecorated(false);
           int response = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete this appointment?", 
           "Remove Appointment", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE);
           if (response == JOptionPane.YES_OPTION){   
                try{
                    database.stmt = database.conn.createStatement();
                    int numUpdated = database.stmt.executeUpdate( "DELETE FROM APPOINTMENT WHERE APPOINTMENTID = " + picked );
                }catch (Exception ex){}
               JOptionPane.showMessageDialog(null,"This appointment has been successfully deleted.");
               MainFrame.panel.removeAll();
               MainFrame.appointmentsPage.page = 1;
               MainFrame.appointmentsPage.Draw();
               MainFrame.header.Draw();
               MainFrame.panel.add(MainFrame.backgroundImage);
               MainFrame.panel.repaint();
           }
        }
      else if(e.getSource() == btnPatient){
           try{
                        database.stmt = database.conn.createStatement();
                        
                        database.rs = database.stmt.executeQuery( 
                                "SELECT * FROM APPOINTMENT p,PATIENT q, SERVICE r WHERE p.SERVICEID = r.SERVICEID" 
                                + " AND p.PATIENTID = q.PATIENTID AND p.APPOINTMENTID = " + picked);
                                
                          while( database.rs.next() )
                               patientsPage.picked = database.rs.getInt("patientId");
                                
                        MainFrame.page = 3;
                        MainFrame.panel.removeAll();
                        MainFrame.patientsPage.page = 2;
                        MainFrame.patientsPage.Draw();
                        MainFrame.header.Draw();
                        MainFrame.panel.add(MainFrame.backgroundImage);
                        MainFrame.panel.repaint();
           }catch(Exception ex){}
        
        }
     }
   }
   
    public class ComboBoxListener implements ActionListener{
        public void actionPerformed(ActionEvent e) {
            JComboBox cb = (JComboBox) e.getSource();
                if(e.getSource() == searchFilter){
                    int comboboxpic=cb.getSelectedIndex();
                    if(comboboxpic == 0){
                        searchBox.setVisible(true);
                        searchBox.setEnabled(false);
                        filter.setVisible(false);
                    }
                    if(comboboxpic == 1){
                        searchBox.setVisible(false);
                        filter.setVisible(true);
                        
                        try{
                        /* Service Combobox */
                        MainFrame.database.stmt = MainFrame.database.conn.createStatement();
                        MainFrame.database.rs = MainFrame.database.stmt.executeQuery("SELECT * FROM SERVICE ORDER BY SERVICENAME");
                               
                        servicepicked.clear();
                        filter.removeAllItems();
                        while(MainFrame.database.rs.next() ){
                           servicepicked.add(MainFrame.database.rs.getInt("serviceId"));
                           filter.addItem(MainFrame.database.rs.getString("serviceName"));
                        }
                       }catch(Exception ex){}
                       
                    }
                    
                    else if(comboboxpic == 2){
                        searchBox.setVisible(false);
                        filter.setVisible(true);
                        
                         try{
                        /* Service Combobox */
                        MainFrame.database.stmt = MainFrame.database.conn.createStatement();
                        MainFrame.database.rs = MainFrame.database.stmt.executeQuery("SELECT * FROM PATIENT ORDER BY LASTNAME");
                               
                        patientpicked.clear();
                        filter.removeAllItems();
                        while(MainFrame.database.rs.next() ){
                           patientpicked.add(MainFrame.database.rs.getInt("patientId"));
                           filter.addItem(MainFrame.database.rs.getString("lastName")+", "+MainFrame.database.rs.getString("firstName"));
                        }
                       }catch(Exception ex){}
                       
                    }
                    else if(comboboxpic == 3){

                    }
                    searchBox.setText("");
                }
        }
   }
   
   /* Listener for Appointments Table */
   class TableListener extends MouseAdapter {
   public void mouseClicked(MouseEvent e) {
       picked = 0;
      if (e.getClickCount() == 2 && e.getSource() == table) {
         JTable target = (JTable)e.getSource();
         
         // To know the exact location of the mouse click
         picked = key.get(target.getSelectedRow());
         
         // Go to Appointment details on mouse click
          MainFrame.panel.removeAll();
          MainFrame.appointmentsPage.page = 2;
          MainFrame.appointmentsPage.Draw();
          MainFrame.header.Draw();
          MainFrame.panel.add(MainFrame.backgroundImage);
          MainFrame.panel.repaint();
         }
   }
   }
  
}

